﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StartStop.ServiceResources;
using System.Data.SqlClient;
using System.Data;

namespace StartStop.Helpers
{
    public class UserStatLog
    {
        public const string LiveConnectionString = "data source=v2.startstop.me;initial catalog=startstop;persist security info=True;user id=sa;password=PASSWORD;";

        /// <summary>
        /// Updates a user stat in the list. 
        /// Currently this does not account for stats which are by specific time period. 
        /// </summary>
        /// <param name="oUserStat"></param>
        /// <returns></returns>
        public static MessageResponse UpdateUserStatLog(ref ServiceResources.UserStatLog oUserStat)
        {
            //TO DO : Add try catch! 

            MessageResponse oResponse = new MessageResponse();
            bool _passedChecks = true;

            // We need to look up the userID from the user GUID.
            Int64 _userID = StartStop.Helpers.User.LookUpUserIDFromGUID(oUserStat.UserGuid);
            // User cannot be found so return fail.
            if (_userID == -1)
            {
                oResponse.Message = "Cannot find the user from the passed GUID";
                oResponse.Success = false;

                _passedChecks = false;
            }
            Int64 _detailedStatID = StartStop.Helpers.DetailedStat.LookUpDetailedStatIDfromGuid(oUserStat.DetailedStatGuid);
            if (_detailedStatID == -1)
            {
                oResponse.Message = "Cannot find detail stat ID from the passed GUID";
                oResponse.Success = false;
                _passedChecks = false;
            }

            // We need to look up the detailstatID from detailstat GUID

            

            // We need to check to make sure this is not an update
            //oUserStat.DetailedStatLogID!=""

            if (_passedChecks)
            {

                SqlConnection conn = new SqlConnection(LiveConnectionString);
                conn.Open();

                SqlCommand cmd = new SqlCommand(@"
                    insert into DetailStatUserLog   (UserID, DetailStatID, Count, StatPercent, CreatedOn, Note, OverviewGUID)
                    Values (@UserID, @DetailStatID, @Count, @StatPercent, @CreatedOn, @Note, @OverviewGUID); select @@IDENTITY;", conn);

                

                cmd.Parameters.AddWithValue("@UserID", _userID);
                cmd.Parameters.AddWithValue("@DetailStatID", _detailedStatID);
                cmd.Parameters.AddWithValue("@OverviewGUID", oUserStat.DetailedStatOverviewGUID);
                cmd.Parameters.AddWithValue("@Count", oUserStat.Count);
                cmd.Parameters.AddWithValue("@StatPercent", oUserStat.Percent);
                cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now);

                // Make sure if we do pass an empty note then it doesn't flow everything up. 
                // This has not been tested. 
                if (oUserStat.Note == null)
                {
                    cmd.Parameters.AddWithValue("@Note", DBNull.Value); 
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Note", oUserStat.Note);
                }
                // Load the ID back in. 

                oUserStat.DetailedStatLogID = long.Parse(cmd.ExecuteScalar().ToString());
                // The update has executed correctly.
                oResponse.Success = true;
                // Populate the response message as well. 
                oResponse.ReturnID = oUserStat.DetailedStatLogID;
                conn.Close();
            }
            return oResponse;
        }

        #region Rank Related Ops
        /// <summary>
        /// This will return the rank of a user based on the count in ascending order, i.e. highest count higehr ranks
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="UserStatID"></param>
        public static Int64 ReturnRankForCountForUserStatLog(Int64 userID, Int64 UserStatID, Int64 UsersCount)
        {

            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(@"   Select COUNT(*) from DetailStatUserLog 
                                                 where UserID<>@UserID and DetailStatID = @UserStat and Count>@UsersCount
                    ", conn);
            cmd.Parameters.AddWithValue("@UserID", userID);
            cmd.Parameters.AddWithValue("@UserStat", UserStatID);
            cmd.Parameters.AddWithValue("@UsersCount", UsersCount);

            // This should give us the number of users above the current user
            string _result = cmd.ExecuteScalar().ToString();

            int _usersRank = int.Parse(_result);
            // Need to add one to the rank
            _usersRank++;

            conn.Close();
            return _usersRank;
        }
        #endregion 

        public static ServiceResources.UserStatLog ReturnSingleStatLogFromDetailStatID(Int64 userID, Int64 UserStatID)
        {
            ServiceResources.UserStatLog oLog = new ServiceResources.UserStatLog();
            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(@" select top 1 * from   DetailStatUserLog
                    where USerID = @UserID and DetailStatID = @StatID
                    order by CreatedOn desc          
                    ", conn);

            cmd.Parameters.AddWithValue("@UserID", userID);
            cmd.Parameters.AddWithValue("@StatID", UserStatID);
            DataTable _returnDataTable = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);
            if (_returnDataTable.Rows.Count > 0)
            {
                foreach (DataRow oRow in _returnDataTable.Rows)
                {
                    /// This if the type is a count 
                    if (oRow["Count"].ToString() != "")
                    {
                        oLog.Count = Int64.Parse(oRow["count"].ToString());
                    }
                    if (oRow["CreatedOn"].ToString() != "")
                    {
                        oLog.LoggedDate = (DateTime)oRow["CreatedOn"];
                    }

                    if (oRow["Note"].ToString() != "")
                    {
                        oLog.Note = oRow["Note"].ToString();
                    }

                    if (oRow["StatPercent"].ToString() != "")
                    {
                        oLog.Percent = Decimal.Parse(oRow["statpercent"].ToString());
                    }
                    // We need to finish implementing this! 
                    // There are fields left. 

                }
                conn.Close();
                // Return the row
                return oLog;
            }
            else
            {
                conn.Close();
                return null;
            }

        }

        /// <summary>
        /// Returns a list of Overview Stats which have at least one entry in the stat list. 
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static List<StatOverView> ReturnActiveStats(Int64 UserID)
        {
            if (UserID > 0)
            {

                SqlConnection conn = new SqlConnection(LiveConnectionString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(@"
                    select distinct do.OverviewGUID, do.Title, do.Description, do.OverviewID from DetailStatUserLog ds 
                    inner join DetailStatOverview do on ds.OverViewGUID = do.OverviewGUID
                    where
	                    ds.UserID = @UserID and 
	                    ds.OverViewGUID is not null
                    ", conn);

                cmd.Parameters.AddWithValue("@UserID", UserID);
                DataTable _returnDataTable = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(_returnDataTable);
                // always close the connection. :) 
                List<StatOverView> oList = new List<StatOverView>();
                #region Map DataTable to list
                foreach (DataRow oRow in _returnDataTable.Rows)
                {
                    StatOverView oOverview = new StatOverView();
                    oOverview.Title = oRow["title"].ToString();
                    oOverview.Description = oRow["description"].ToString();
                    oOverview.OverviewID = (Int64)oRow["OverviewID"];
                    oOverview.OverviewGUID = Guid.Parse(oRow["OverviewGUID"].ToString());
                    oList.Add(oOverview);
                }
                #endregion
                conn.Close();
                return oList;
            }

            return null;
        }


        public static List<StartStop.ServiceResources.StatDescription> ReturnListOfStatsForOverview(Int64 OverviewID)
        {
            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from DetailStat where OverviewID = @OverviewID", conn);
            cmd.Parameters.AddWithValue("@OverviewID", OverviewID);
            DataTable _returnDataTable = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(_returnDataTable);

            StatDescription oDesc = new StatDescription();

            List<StatDescription> oList = new List<StatDescription>();
            foreach (DataRow oRow in _returnDataTable.Rows)
            {
                oDesc = new StatDescription();
                if (oRow["DeveloperGUID"].ToString() != "")
                {
                    oDesc.DeveloperGUID = Guid.Parse(oRow["DeveloperGUID"].ToString());
                }
                oDesc.DetailStatID = (Int64)oRow["DetailStatID"];
                oDesc.DetailStatGUID = Guid.Parse(oRow["DetailStatGUID"].ToString());
                oDesc.Name = oRow["NameOfStat"].ToString();
                oDesc.DataType = (StatDescription.StatDateType)int.Parse(oRow["DataType"].ToString());
                oDesc.Description = oRow["DetailOfStat"].ToString();
                oDesc.CreatedByType = (StatDescription.StatCreatedTyped)int.Parse(oRow["CreatedByType"].ToString());
                if (oRow["CreatedByUserID"].ToString() != "")
                {
                    // We could look up the GUID here. 
                    //oDesc.c = (Int64)oRow["CreatedBy"];
                }
                if (oRow["CreatedOn"].ToString() != "")
                {
                    oDesc.CreatedOn = (DateTime)oRow["CreatedOn"];
                }
                if (oRow["ModifiedOn"].ToString() != "")
                {
                    oDesc.ModifiedOn = (DateTime)oRow["ModifiedOn"];
                }
                oDesc.OverviewID = (Int64)oRow["OverviewID"];

                oList.Add(oDesc);
            }
            return oList;
        }

        #region LookupIDs for OverViews
        public static Int64 ReturnOverviewIDfromGUID(Guid OverviewGUID)
        {
            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select OverviewID from DetailStatOverview where OverviewGUID = @OverviewGUID", conn);
            cmd.Parameters.AddWithValue("@OVerviewGUID", OverviewGUID);
            string _result = cmd.ExecuteScalar().ToString();

            if (_result != "")
            {
                return Int64.Parse(_result);
            }
            else
            {
                return -1;
            }

        }

        /// <summary>
        /// Look up the GUID of an stat overview from a text input
        /// </summary>
        /// <param name="overviewTitle"></param>
        /// <returns></returns>
        public static Guid ReturnOverGUIDfromOverviewTitle(string overviewTitle)
        {

            SqlConnection conn = new SqlConnection(LiveConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select OverviewGUID from DetailStatOverview where Title = @OverviewName", conn);
            cmd.Parameters.AddWithValue("@OVerviewName", overviewTitle);
            string _result = cmd.ExecuteScalar().ToString();
            if (_result != "")
            {
                return Guid.Parse(_result);
            }
            else
            {
                return Guid.Empty;
            }
        }
        #endregion 


        /// <summary>
        /// Returns a dictionary with the user log on one side and the description on the other. 
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static UserStatLogWithDetail ReturnUserStatLogWithDetail(Int64 UserID, Guid OverviewGUID)
        {
            UserStatLogWithDetail oStats = new UserStatLogWithDetail();
            oStats.StatList = new Dictionary<StatDescription, ServiceResources.UserStatLog>();
            UserStatLog oStatLog = new UserStatLog();
            DetailedStat oDetailStat = new DetailedStat();

            // Return the list of stat detail
            List<StartStop.ServiceResources.StatDescription> oDesc = new List<StatDescription>();
            oDesc = ReturnListOfStatsForOverview(ReturnOverviewIDfromGUID(OverviewGUID));

            // Now iterate through the the list and add the value 
            foreach (StatDescription statDetail in oDesc)
            {
                // Look up to see if the user has a value if so, then add to the dictionary, if not, then we can just ignore
                ServiceResources.UserStatLog oLog = new ServiceResources.UserStatLog();
                oLog = Helpers.UserStatLog.ReturnSingleStatLogFromDetailStatID(UserID, statDetail.DetailStatID);
                if (oLog != null)
                {
                    // The user has something, before we add lets see what type it is so we can add a rank
                    if (statDetail.DataType == StatDescription.StatDateType.Number)
                    {
                        // It's a number, so lets run the rank
                        oLog.UserStatRank = ReturnRankForCountForUserStatLog(UserID, statDetail.DetailStatID, oLog.Count); 
                    }
                    oStats.StatList.Add(statDetail, oLog);
                }
            }

            return oStats;
        }

    }
}
